OneStream can connect and import data from any external system using direct database connection to the external system. This means data can be imported and processed all the way through the Workflow certification process without ever having to use a source system extract file.
Connector Business Rule
A Connector Business Rule defines the connection, data result sets, and drill-back option capabilities of an external data connection. A Connector functions as a Business Rule called by a Data Source and reveals what information is required from an external system. See Business Rules in Application Tools for an example of this rule.
Connector Data Source
Fields from the external data query results are mapped to Dimensions creating a processing behavior similar to the behavior of a Delimited File. Using this mapping process enables a Connector Data Source to use all the same built processing capabilities available with file-based Data Sources. This capability enables the design of an external data Connector to be entirely focused on connecting to and reading data from an external source instead of focusing on integrating complex business logic. The specific business logic can be added to the Data Source Dimensions in the form of a Complex Expression or Business Rule. This design methodology will help with writing the Connector Business Rule in a way that requires very little maintenance by business users.
Connector Information Request Types
GetFieldList
This is called by the Data Source designer screen when the user selects a Connector Data Source or one of its defined Dimensions. A list of available fields in the external Data Source will be visible as a list of Vb.Net Strings [List(Of String)] is requested.
GetData
This is called by the Import Workflow task when the Load and Transform button is clicked. The execution of a data query(s) that retrieves the row values for the chosen Workflow Unit is requested.
Fields
The field names returned by this query must match the field names returned by the GetFieldList request.
Where Clause
Typically the active Workflow Unit Time and Scenario values are converted to equivalent criteria values for the Time or Scenario of the external system.
Data Volume
Consider loading summarized data rather than full transaction system data replication because drill back is provided for more detailed values.
GetDrillBackTypes
Drill Back types can deliver results based on the different visualization types. This is called when a user double-clicks or right-clicks and selects Drill Back from a row in the source data load or drill down screens. A set of supported drill-back options to present to the end user as a list of DrillBackTypeInfo objects [List(Of DrillBackTypeInfo)] is requested. Drill Back types provide the Connector designer with the power to provide the end user with a menu list of drill back options.
DataGrid
This presents a grid of data rows to the end user.
TextMessage
This presents a text message to the end user.
WebUrl
This presents a website or custom HTML web content to the end user.
WebUrlPopOutDefaultBrowser
Opens a website or custom HTML web content in an external browser. From the Stage Import data grid, right-click on a data record, and select Drill Back. A dialog presents a menu of pre-configured Drill-back options. When you choose WebUrlPopOutDefaultBrowser, a standard browser session is launched, and you go to a web page based on variables.
FileViewer
This presents file contents to the end user from one of three locations.
FileShareFile
A file located in a folder in the OneStream File Share.
AppDBFile
A file stored in an application database.
SysDBFile
A file stored in a framework (System) database.
GetDrillBack
This is called when a user selects a specific Drill Back type presented by the GetDrillBackTypes request. When this action is executed, the Business Rule arguments will contain a reference to the DrillBackTypeInfo object the user selected which allows the Connector designer to determine how to get proper information to display for the DrillBackTypeInfo.
Connector Integration Prerequisites
The following items provide an overview of the major technology components involved in integrating external systems with deployment.
Determine Source System Inventory
The first step in integrating various source systems is to determine all the ones needed. This includes:
Source System Location & Identification
Database Type and Source System
Oracle, SQL, DB2, Syteline, Newstar, Lawson, PeopleSoft, Access, MAS500, etc.
NOTE: The requirement for Oracle Database integrations is that all Oracle Source System TNS Profile details need to be in place on each of the OneStream application servers.
Data Query Method
Detailed Data Query, Data View, Stored Procedure, etc.
Source System Drill Back Criteria (if required)
Detailed Data Query, Data View, Stored Procedure, etc.
Source System Direct Access Credentials
A read-only type of access needs to be granted for the user account because the data from these external systems will be read. The read-only access should be granted against the productions instance of the data source as the data queries will be used to tie out data and do not present any risk to the source system themselves.
Source System 64-bit Client Data Provider
OneStream is a Microsoft .NET application with a 64-bit architecture. To communicate with any source system, a 64-bit source system client data provider needs to be available and installed on each OneStream application server. The source system’s client data provider is what gives the ability to make an OLEDB or ODBC connection to the system.
Determine Connection String
A connection string specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider to initiate the connection. Whilst commonly used for a database connection, the data source could also be a spreadsheet or text file. The connection string may include attributes such as the name of the driver, server and database, as well as security information such as user name and password.
Create a Connection String from the OneStream Application Server
-
Right-click the Desktop icon of the OneStream application server and select New > Text Document.
-
Name the document and change the file extension from txt to udl.
This creates a Data Link File to assist in the formation of the source system connection string.
-
Determine the DB Provider that the GL Source System is using (e.g. SQL, Oracle, etc.).
-
Determine the server name where the data resides for the GL Source System.
-
Determine the user name and password used to connect to the server for the GL Source System.
-
Determine the database name on the server where the GL Source System data resides.
-
Save the completed UDL file and then rename the extension back to txt from udl.
-
Open the text file to see the connection string provided.
Example Connection Strings
SQL Server
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBName;Data Source=SQLSERVERNAME
ORACLE (11i or R12)
Provider=OraOLEDB.Oracle.1;Password=<xxxxx>;Persist Security Info=True;User ID=<username>;Data Source=frepro.world
DB2
Provider=IBMDA400.DataSource.1;Password=<xxxxx>;Persist Security Info=True;User ID=OSuser;Data Source=HUTCH400;Use SQL Packages=True
MS Access
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\UNCFileShare\DB1.accdb;Mode=Read|Share Deny None;Persist Security Info=False
Determine the Data Query Method
To extract data from any source system, the data query method and facility need to be determined. Data can be queried through a SQL Query, a SQL View, or Stored Procedure. OneStream executes this request against the source system using the defined source system connection string and processes the returned results within OneStream.
For example, if directly pulling in Trial Balance Data is required, then the detailed query that currently makes up the existing Trial Balance Report would be necessary for OneStream to pull the same data.
SQL Query
A SQL Query can be broken down into numerous elements, each beginning with a keyword. Although it is not necessary, a common convention is to write these keywords in all capital letters.
The standard sections of a SQL Query are made up of the following four elements:
SELECT
FROM
WHERE
ORDER BY
The example below is a SQL Query used to pull Trial Balance Data from several different tables in an Oracle Database:
SELECT
GL_SETS_OF_BOOKS.NAME
,GL_BALANCES.ACTUAL_FLAG
,GL_BALANCES.PERIOD_NAME
,GL_BALANCES.PERIOD_NUM
,GL_BALANCES.PERIOD_YEAR
,GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
,GL_CODE_COMBINATIONS.SEGMENT1
,GL_CODE_COMBINATIONS.SEGMENT2
,GL_CODE_COMBINATIONS.SEGMENT3
,GL_CODE_COMBINATIONS.SEGMENT4
,GL_CODE_COMBINATIONS.SEGMENT5
,GL_CODE_COMBINATIONS.SEGMENT6
,GL_CODE_COMBINATIONS.SEGMENT7
,GL_CODE_COMBINATIONS.SEGMENT8
,GL_CODE_COMBINATIONS.SEGMENT9
,GL_CODE_COMBINATIONS.SEGMENT10
,SUM( NVL(GL_BALANCES.BEGIN_BALANCE_DR,0) - NVL(GL_BALANCES.BEGIN_BALANCE_CR,0))"OPEN BAL"
,NVL(GL_BALANCES.PERIOD_NET_DR,0) "DEBIT"
,NVL(GL_BALANCES.PERIOD_NET_CR,0) "CREDIT"
,SUM( NVL(GL_BALANCES.PERIOD_NET_DR,0) - NVL(GL_BALANCES.PERIOD_NET_CR,0))"NET MOVEMENT"
,SUM(( NVL(GL_BALANCES.PERIOD_NET_DR,0) + NVL(GL_BALANCES.BEGIN_BALANCE_DR,0))) - SUM (NVL(GL_BALANCES.PERIOD_NET_CR,0)+NVL(GL_BALANCES.BEGIN_BALANCE_CR,0))"CLOSE BAL"
,GL_BALANCES.CURRENCY_CODE
,GL_BALANCES.TRANSLATED_FLAG
,GL_BALANCES.TEMPLATE_ID
,FND_FLEX_VALUES_VL.FLEX_VALUE ,FND_FLEX_VALUES_VL.DESCRIPTION
,FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID FROM GL_BALANCES, GL_CODE_COMBINATIONS, GL_SETS_OF_BOOKS, FND_FLEX_VALUES_VL WHERE GL_CODE_COMBINATIONS.CODE_COMBINATION_ID = GL_BALANCES.CODE_COMBINATION_ID AND GL_BALANCES.ACTUAL_FLAG = 'A' AND GL_BALANCES.CURRENCY_CODE = GL_SETS_OF_BOOKS.CURRENCY_CODE AND GL_BALANCES.LEDGER_ID = GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID AND GL_BALANCES.TEMPLATE_ID IS NULL AND GL_BALANCES.PERIOD_NAME = 'Jul-14' AND FND_FLEX_VALUES_VL.FLEX_VALUE = GL_CODE_COMBINATIONS.SEGMENT4 AND FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = '101432874' AND GL_CODE_COMBINATIONS.SEGMENT2 IN (2050, 2100, 2200, 2300, 2400, 2500) GROUP BY GL_SETS_OF_BOOKS.NAME ,GL_BALANCES.ACTUAL_FLAG ,GL_BALANCES.PERIOD_NAME ,GL_BALANCES.PERIOD_NUM ,GL_BALANCES.PERIOD_YEAR ,GL_CODE_COMBINATIONS.CODE_COMBINATION_ID ,GL_CODE_COMBINATIONS.SEGMENT1 ,GL_CODE_COMBINATIONS.SEGMENT2 ,GL_CODE_COMBINATIONS.SEGMENT3 ,GL_CODE_COMBINATIONS.SEGMENT4 ,GL_CODE_COMBINATIONS.SEGMENT5 ,GL_CODE_COMBINATIONS.SEGMENT6 ,GL_CODE_COMBINATIONS.SEGMENT7 ,GL_CODE_COMBINATIONS.SEGMENT8 ,GL_CODE_COMBINATIONS.SEGMENT9 ,GL_CODE_COMBINATIONS.SEGMENT10 ,NVL(GL_BALANCES.PERIOD_NET_DR,0) ,NVL(GL_BALANCES.PERIOD_NET_CR,0) ,GL_BALANCES.CURRENCY_CODE ,GL_BALANCES.TRANSLATED_FLAG ,GL_BALANCES.TEMPLATE_ID ,FND_FLEX_VALUES_VL.FLEX_VALUE ,FND_FLEX_VALUES_VL.DESCRIPTION ,FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID HAVING SUM(( NVL(GL_BALANCES.PERIOD_NET_DR,0) + NVL(GL_BALANCES.BEGIN_BALANCE_DR,0))) - SUM(NVL(GL_BALANCES.PERIOD_NET_CR,0)+NVL(GL_BALANCES.BEGIN_BALANCE_CR,0)) <> 0
SQL View
In many cases, creating a SQL View of data to provide information to OneStream is a more preferred option and typically simplifies the complexity of the query.
In the example below, the customer can combine several data tables required in the source system, and present the data in one View for OneStream to query:
SELECT SEGMENT1 As Entity SEGMENT2 As Establishment SEGMENT3 As France_Account SEGMENT4 As US_Account SEGMENT5 As Cost_Center SEGMENT6 As Family SEGMENT7 As Product_Line SEGMENT8 As Interco SEGMENT9 As Future PERIOD_YEAR As Year PERIOD_MONTH As Month CURRENCY_CODE As Currency_Code CLOSE_NET_BALANCE As Net_Balance SET_OF_BOOKS_ID As Set_Of_Books_ID FROM APPS.XXSWM_ONESTREAM_GL_BALANCES
Stored Procedure
The example below is a SQL Stored Procedure used to pull Trial Balance Data from several different tables in a SQL Database.
In this example, the Entity, Year, and Period are passed to the Stored Procedure:
spGLCalcTrialBalance 'ASCC', '2013', 6
Apply Connection String to XFAppServerConfig.xml File
When the connection string is created, then the database connections can be centralized in the Server Configuration under the App Server Configuration File. Under Databases, click on (Collection) for Database Server Connections and the Database Server Connections will appear. The string will then be placed in the Connection String under Connection String Settings. The name of the connection string will be used as part of the source connector.
Defining External Data Connections
Application Server Configuration File
Creating Named External ODBC / OLEDB Connection
Step 1: Required ODBC/OLEDB Connection Software
Any client ODBC/OLEDB drivers must be installed on each application server for the OneStream application to make a connection to the external database. This way the administrator knows what type of database engine contains which Data Source.
Step 2: Creating the Connection String
The application server configuration file must be modified to add a named external database connection that can be used by the Connector Business Rule and custom reports.
Example of the Server Configuration Utility:
Step 3: Creating an External Database Test Query
The best way to prototype the queries needed to create a Connector Business Rule is to create a set of Dashboard Data Adapters to be used as a test bed.
As a best practice, create a new Dashboard Maintenance Unit named EXS The Connector Name. The prefix EXS stands for External System and will provide administrators with an immediate understanding of the Maintenance Unit’s contents. The three steps below explain how to create this.
Step 1
Create a new Data Adapter for each type of query needed to proto type (GetFieldList, SelectData, Drill Back, etc.)
Example of a Data Adapter being used to get all fields in the source table of the external database connection:
Step 2
Click 
in the Dashboard administration toolbar to test the query.
Step 3
Evaluate the results of the query. The Data Adapter test only returns a small subset of rows from the query, but it specifies the actual number of rows that will return during an actual query execution.
Building Data Connectors
ODBC / OLEDB Connectors
GetFieldList
Select Query against the external database. There will be a manual list of strings returned for each field.
GetData
The selected statement should match GetFieldList. Add criteria for Scenario and Time and map the OneStream Workflow Unit Scenario and Time values to corresponding values in the source system as a Where Clause criteria value.
GetDrillBackTypes
This shows the set of drill back options provided to the user.
GetDrillBack
This executes the selected drill back type for the current source data row.
Custom API Connectors
Uses OneStream's External Server Technology. IIS needs to be recycled on all application servers and followed by all web servers after adding the external named connection.
Drill Back
Using a SQL connector allows a user to drill back to a source system and show detailed records from a document, PDF, website. The Connector Data Source, configured by the author, provides a menu of data viewing options such as Year to Date, Month to Date, Invoice Documents or Material Type Detail. Utilizing this feature can reduce the amount of data imported into the Financial Model by allowing analysis to occur at the source system.
Viewing Data
After data is loaded into the Stage, a user can right-click on a data row and select Drill Back. This will bring up the pre-configured options from which the user can choose.
If more detail is needed, another level of Drill Back can be performed. This is configured in the Connector Business Rule and can drill back and around source systems. These nested drill paths can provide as much detail as an application requires.
Key API, Args, or BRAPI Examples
Namespace OneStream.BusinessRule.Connector.RevenueMgmtHouston Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals,
ByVal api As Transformer, ByVal args As ConnectorArgs) As Object Try 'Get the query information Dim connectionString As String = GetConnectionString(si, globals, api) 'Get the Field name list or load the data Select Case args.ActionType Case Is = ConnectorActionTypes.GetFieldList 'Return Field Name List Dim fieldListSQL As String = GetFieldListSQL(si, globals, api) Return api.Parser.GetFieldNameListForSQLQuery(si, DbProviderType.OLEDB,
connectionString, true, fieldListSQL, false)
Case Is = ConnectorActionTypes.GetData
'Process Data
Dim sourceDataSQL As String = GetSourceDataSQL(si, globals, api)
api.Parser.ProcessSQLQuery(si, DbProviderType.OLEDB, connectionString, true,
sourceDataSQL, false, api.ProcessInfo)
Return Nothing
Case is = ConnectorActionTypes.GetDrillBackTypes
'Return the list of Drill Types (Options) to present to the end user
Return Me.GetDrillBackTypeList(si, globals, api, args)
Case Is = ConnectorActionTypes.GetDrillBack
'Process the specific Drill-Back type
Return Me.GetDrillBack(si, globals, api, args, args.DrillBackType.DisplayType,
connectionString)
End Select
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'Create a Connection string to the External Database
Private Function GetConnectionString(ByVal si As SessionInfo, ByVal globals As BRGlobals,
ByVal api As Transformer) As String
Try
'Connection String Method
'-----------------------------------------------------------
' Dim connection As New Text.StringBuilder'
' connection.Append("Provider=SQLOLEDB.1;")
' connection.Append("Data Source=LocalHost\MSSQLSERVER2008;")
' connection.Append("Initial Catalog=SampleData;")
' connection.Append("Integrated Security=SSPI")
' Return connection.ToString
'Named External Connection
'-----------------------------------------------------------
Return "Revenue Mgmt System"
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'Create the field list SQL Statement
Private Function GetFieldListSQL(ByVal si As SessionInfo, ByVal globals As BRGlobals,
ByVal api As Transformer) As String
Try
'Create the SQL Statement
Dim sql As New Text.StringBuilder
sql.Append("SELECT Top(1)")
sql.Append("TransID, PlantCode, CustId, CustName, InvNo, InvYear, InvMonth, InvDesc, GLAccount, WorkDay, ProdModel, BomCode, UnitPrice, Units,
Amount, DestinationCode ")
sql.Append("FROM InvoiceDocumentDetail ")
Return sql.ToString
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'Create the data load SQL Statement
Private Function GetSourceDataSQL(ByVal si As SessionInfo, ByVal globals As BRGlobals,
ByVal api As Transformer) As String
Try
'Create the SQL Statement
Dim statement As New Text.StringBuilder
Dim selectClause As New Text.StringBuilder
Dim fromClause as New Text.StringBuilder
Dim whereClause as New Text.StringBuilder
Dim orderByClause as New Text.StringBuilder
selectClause.Append("SELECT ")
selectClause.Append("TransID, PlantCode, CustId, CustName, InvNo, InvYear, InvMonth, InvDesc, GLAccount, WorkDay, ProdModel, BomCode, UnitPrice,Units, Amount,
DestinationCode ")
fromClause.Append("FROM InvoiceDocumentDetail ")
whereClause.Append("WHERE ")
'Get the YEAR from the current XF Workflow Unit TimeKey
whereClause.Append("(")
whereClause.Append("InvYear = " & TimeDimHelper.GetYearFromId(api.WorkflowUnitPk.TimeKey).ToString)
whereClause.Append(")")
'Get the MONTH from the current XF Workflow Unit TimeKey
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("InvMonth = 'M" & TimeDimHelper.GetSubComponentsFromId
(api.WorkflowUnitPk.TimeKey)
.Month.ToString & "'")
whereClause.Append(") ")
'Select Houston Plant Codes
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("PlantCode IN('H200','H210')")
whereClause.Append(") ")
orderByClause.Append("ORDER BY ")
orderByClause.Append("PlantCode, CustId, WorkDay, ProdModel, DestinationCode")
'Create the full SQL Statement
statement.Append(selectClause.ToString)
statement.Append(fromClause.ToString)
statement.Append(whereClause.ToString)
statement.Append(orderByClause.ToString)
Return statement.ToString
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'Create the drill back options list
Private Function GetDrillBackTypeList(ByVal si As SessionInfo,ByVal globals As BRGlobals, ByVal api As Transformer, ByVal args As ConnectorArgs) As List(Of DrillBackTypeInfo) Try 'Create the SQL Statement Dim drillTypes As New List(Of DrillBackTypeInfo) drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.FileShareFile,
New NameAndDesc("Invoice Document","Invoice Document")))
drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("Material Type Detail","Material Type Detail")))
Return drillTypes
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'Execute specific drill back type
private Function GetDrillBack(ByVal si As SessionInfo, ByVal globals As BRGlobals,ByVal api As Transformer,
ByVal args As ConnectorArgs, ByVal drillBackType As ConnectorDrillBackDisplayTypes,
ByVal connectionString as String) As DrillBackResultInfo Try Select case drillBackType case is = ConnectorDrillBackDisplayTypes.FileShareFile 'Show FileShare File Dim drillBackInfo as new DrillBackResultInfo drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.FileShareFile drillBackInfo.DocumentPath = Me.GetDrillBackDocPath(si, globals, api, args) return drillBackInfo case is = ConnectorDrillBackDisplayTypes.DataGrid 'Return Drill Back Detail Dim drillBackSQL As String = GetDrillBackSQL(si, globals, api, args) Dim drillBackInfo as new DrillBackResultInfo drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.DataGrid drillBackInfo.DataTable = api.Parser.GetXFDataTableForSQLQuery(si,
DbProviderType.OLEDB, connectionString, true, drillBackSQL,
false, args.PageSize, args.PageNumber) return drillBackInfo case else return Nothing End Select Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function 'Create the drill back Document Path Private Function GetDrillBackDocPath(ByVal si As SessionInfo,
ByVal globals As BRGlobals,ByVal api As Transformer, ByVal args As ConnectorArgs) As String Try 'Get the values for the source row that we are drilling back to Dim sourceValues as Dictionary(Of string, Object) =
api.Parser.GetFieldValuesForSourceDataRow(si, args.RowID) If (Not sourceValues Is Nothing) And (sourceValues.Count > 0) then Return "Applications/GolfStream_v24/DataManagement/RevenueMgmtInvoices/"
& sourceValues.Item(StageConstants.MasterDimensionNames.Attribute1).ToString & ".pdf" Else Return String.Empty End If Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function 'Create the drill back SQL Statement Private Function GetDrillBackSQL(ByVal si As SessionInfo, ByVal globals As BRGlobals,
ByVal api As Transformer, ByVal args As ConnectorArgs) As String Try 'Get the values for the source row that we are drilling back to Dim sourceValues as Dictionary(Of string, Object) =
api.Parser.GetFieldValuesForSourceDataRow(si, args.RowID)
If (Not sourceValues Is Nothing) And (sourceValues.Count > 0) then
Dim statement As New Text.StringBuilder
Dim selectClause As New Text.StringBuilder
Dim fromClause as New Text.StringBuilder
Dim whereClause as New Text.StringBuilder
Dim orderByClause as New Text.StringBuilder
'Create the SQL Statement
selectClause.Append("SELECT ")
selectClause.Append("CustName, InvDesc, BomCode, UnitPrice, Units,Amount ")
fromClause.Append("FROM InvoiceMaterialDetail ")
whereClause.Append("WHERE ")
'Get the YEAR from the source record
whereClause.Append("(")
whereClause.Append("InvYear = " & TimeDimHelper.GetYearFromId(sourceValues.Item
(StageTableFields.StageSourceData.DimWorkflowTimeKey).ToString))
whereClause.Append(")")
'Get the MONTH from the source record
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("InvMonth = 'M" & TimeDimHelper.GetSubComponentsFromId(sourceValues.Item
(StageTableFields.StageSourceData.DimWorkflowTimeKey))
.Month.ToString & "'")
whereClause.Append(")")
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("PlantCode = '" & sourceValues.Item
(StageConstants.MasterDimensionNames.Entity)
.ToString & "'")
whereClause.Append(")")
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("InvNo = '" & sourceValues.Item
(StageConstants.MasterDimensionNames.Attribute1)
.ToString & "'")
whereClause.Append(")")
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("ProdModel = '" & sourceValues.Item
(StageConstants.MasterDimensionNames.UD2).ToString& "'")
whereClause.Append(")")
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("DestinationCode = '" & sourceValues.Item
(StageConstants.MasterDimensionNames.UD3).
ToString & "'")
whereClause.Append(")")
whereClause.Append(" And ")
whereClause.Append("(")
whereClause.Append("CustID = '" &sourceValues.Item(StageConstants.MasterDimensionNames.UD4)
.ToString & "'")
whereClause.Append(")")
orderByClause.Append("ORDER BY ")
orderByClause.Append("BomCode")
'Create the full SQL Statement
statement.Append(selectClause.ToString)
statement.Append(fromClause.ToString)
If args.ClientFilterRequest.length > 0 then
statement.Append(whereClause.ToString)
statement.Append(" And ")
statement.Append(args.ClientFilterRequest)
Else
statement.Append(whereClause.ToString)
End If
If args.ClientSortRequest.Length > 0 then
statement.Append(args.ClientSortRequest)
Else
statement.Append(orderByClause.ToString)
End if
'ErrorHandler.LogMessage(si, statement.ToString)
Return statement.ToString
Else
Return String.Empty
End If
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
